Development of SQLite3 database operation details [connection query insert update delete close etc.]
- 2020-06-12 09:59:19
- OfStack
This article describes the Python development SQLite3 database operations. To share for your reference, specific as follows:
'''SQLite The database is 1 A very small embedded open source database software, that is
There is no separate maintenance process; all maintenance comes from the program itself.
in python In the use sqlite3 Creates a connection to the database when the specified database file does not exist
The connection object automatically creates the database file; If the database file already exists, the connection object will not be created again
Instead, open the database file directly.
The connection object can be a database file on the hard disk, or it can be an in-memory, in-memory database
After performing any operation, there is no need to commit the transaction (commit)
Create on hard disk: conn = sqlite3.connect('c:\\test\\test.db')
Create above memory: conn = sqlite3.connect('"memory:')
Below we 1 Create a database file on the hard disk as an example to specify:
conn = sqlite3.connect('c:\\test\\hongten.db')
Among them conn An object is a database linked object, and for a database linked object, you have the following operations:
commit() -- Transaction commit
rollback() -- Transaction rollback
close() -- Shut down 1 Number of database links
cursor() -- create 1 A cursor
cu = conn.cursor()
So we've created it 1 A cursor object: cu
in sqlite3 In which all sql Statements are executed with the participation of the cursor object
For cursor objects cu , with the following specific operations:
execute() -- perform 1 article sql statements
executemany() -- To perform multiple sql statements
close() -- Cursor is closed
fetchone() -- Take it out of the results 1 records
fetchmany() -- Multiple records are extracted from the results
fetchall() -- Extract all records from the results
scroll() -- The cursor rolling
'''
Here is the demo I made. In demo, I made a very detailed comment and demonstration of the functions. The details are as follows:
When SHOW_SQL = False:
Python 3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> ================================ RESTART ================================
>>>
show_sql : False
Delete database table tests ...
Hard drive :[c:\test\hongten.db]
Delete database tables [student] successful !
Create database table tests ...
Hard drive :[c:\test\hongten.db]
Creating database tables [student] successful !
Save data test ...
Hard drive :[c:\test\hongten.db]
Query all data ...
Hard drive :[c:\test\hongten.db]
(1, 'Hongten', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62')
(2, 'Tom', ' male ', 22, ' San Francisco, USA ', '15423****63')
(3, 'Jake', ' female ', 18, ' Guangzhou city, Guangdong Province ', '18823****87')
(4, 'Cate', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')
##################################################
The query 1 The data ...
Hard drive :[c:\test\hongten.db]
(1, 'Hongten', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62')
##################################################
Update the data ...
Hard drive :[c:\test\hongten.db]
Query all data ...
Hard drive :[c:\test\hongten.db]
(1, 'HongtenAA', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62')
(2, 'HongtenBB', ' male ', 22, ' San Francisco, USA ', '15423****63')
(3, 'HongtenCC', ' female ', 18, ' Guangzhou city, Guangdong Province ', '18823****87')
(4, 'HongtenDD', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')
##################################################
Delete the data ...
Hard drive :[c:\test\hongten.db]
Query all data ...
Hard drive :[c:\test\hongten.db]
(2, 'HongtenBB', ' male ', 22, ' San Francisco, USA ', '15423****63')
(4, 'HongtenDD', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')
>>>
When SHOW_SQL = True:
Python 3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> ================================ RESTART ================================
>>>
show_sql : True
Delete database table tests ...
Hard drive :[c:\test\hongten.db]
perform sql:[DROP TABLE IF EXISTS student]
Delete database tables [student] successful !
Create database table tests ...
Hard drive :[c:\test\hongten.db]
perform sql:[CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`gender` varchar(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
)]
Creating database tables [student] successful !
Save data test ...
Hard drive :[c:\test\hongten.db]
perform sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)], parameter :[(1, 'Hongten', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62')]
perform sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)], parameter :[(2, 'Tom', ' male ', 22, ' San Francisco, USA ', '15423****63')]
perform sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)], parameter :[(3, 'Jake', ' female ', 18, ' Guangzhou city, Guangdong Province ', '18823****87')]
perform sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)], parameter :[(4, 'Cate', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')]
Query all data ...
Hard drive :[c:\test\hongten.db]
perform sql:[SELECT * FROM student]
(1, 'Hongten', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62')
(2, 'Tom', ' male ', 22, ' San Francisco, USA ', '15423****63')
(3, 'Jake', ' female ', 18, ' Guangzhou city, Guangdong Province ', '18823****87')
(4, 'Cate', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')
##################################################
The query 1 The data ...
Hard drive :[c:\test\hongten.db]
perform sql:[SELECT * FROM student WHERE ID = ? ], parameter :[1]
(1, 'Hongten', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62')
##################################################
Update the data ...
Hard drive :[c:\test\hongten.db]
perform sql:[UPDATE student SET name = ? WHERE ID = ? ], parameter :[('HongtenAA', 1)]
perform sql:[UPDATE student SET name = ? WHERE ID = ? ], parameter :[('HongtenBB', 2)]
perform sql:[UPDATE student SET name = ? WHERE ID = ? ], parameter :[('HongtenCC', 3)]
perform sql:[UPDATE student SET name = ? WHERE ID = ? ], parameter :[('HongtenDD', 4)]
Query all data ...
Hard drive :[c:\test\hongten.db]
perform sql:[SELECT * FROM student]
(1, 'HongtenAA', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62')
(2, 'HongtenBB', ' male ', 22, ' San Francisco, USA ', '15423****63')
(3, 'HongtenCC', ' female ', 18, ' Guangzhou city, Guangdong Province ', '18823****87')
(4, 'HongtenDD', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')
##################################################
Delete the data ...
Hard drive :[c:\test\hongten.db]
perform sql:[DELETE FROM student WHERE NAME = ? AND ID = ? ], parameter :[('HongtenAA', 1)]
perform sql:[DELETE FROM student WHERE NAME = ? AND ID = ? ], parameter :[('HongtenCC', 3)]
Query all data ...
Hard drive :[c:\test\hongten.db]
perform sql:[SELECT * FROM student]
(2, 'HongtenBB', ' male ', 22, ' San Francisco, USA ', '15423****63')
(4, 'HongtenDD', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')
>>>
Specific code:
#python sqlite
#Author : Hongten
#Create : 2013-08-09
#Version: 1.0
#DB-API 2.0 interface for SQLite databases
import sqlite3
import os
'''SQLite The database is 1 A very small embedded open source database software, that is
There is no separate maintenance process; all maintenance comes from the program itself.
in python In the use sqlite3 Creates a connection to the database when the specified database file does not exist
The connection object automatically creates the database file; If the database file already exists, the connection object will not be created again
Instead, open the database file directly.
The connection object can be a database file on the hard disk, or it can be an in-memory, in-memory database
After performing any operation, there is no need to commit the transaction (commit)
Create on hard disk: conn = sqlite3.connect('c:\\test\\test.db')
Create above memory: conn = sqlite3.connect('"memory:')
Below we 1 Create a database file on the hard disk as an example to specify:
conn = sqlite3.connect('c:\\test\\hongten.db')
Among them conn An object is a database linked object, and for a database linked object, you have the following operations:
commit() -- Transaction commit
rollback() -- Transaction rollback
close() -- Shut down 1 Number of database links
cursor() -- create 1 A cursor
cu = conn.cursor()
So we've created it 1 A cursor object: cu
in sqlite3 In which all sql Statements are executed with the participation of the cursor object
For cursor objects cu , with the following specific operations:
execute() -- perform 1 article sql statements
executemany() -- To perform multiple sql statements
close() -- Cursor is closed
fetchone() -- Take it out of the results 1 records
fetchmany() -- Multiple records are extracted from the results
fetchall() -- Extract all records from the results
scroll() -- The cursor rolling
'''
#global var
# Database file terminative path
DB_FILE_PATH = ''
# The name of the table
TABLE_NAME = ''
# Whether or not to print sql
SHOW_SQL = True
def get_conn(path):
''' Gets the connection object to the database as the absolute path to the database file
If the passed parameter exists and is a file, then go back to the hard disk
The connection object of the database file under the path; Otherwise, it returns a data connection in memory
Connection object '''
conn = sqlite3.connect(path)
if os.path.exists(path) and os.path.isfile(path):
print(' Hard drive :[{}]'.format(path))
return conn
else:
conn = None
print(' On the memory :[:memory:]')
return sqlite3.connect(':memory:')
def get_cursor(conn):
''' The method is to get the cursor object of the database, the parameter is the connection object of the database
If the database connection object is not None , returns the database connection object created
Create a cursor object; Otherwise returns 1 A cursor object, which is data in memory
The cursor object created by the library connection object '''
if conn is not None:
return conn.cursor()
else:
return get_conn('').cursor()
###############################################################
#### create | Delete table operation START
###############################################################
def drop_table(conn, table):
''' If the table exists , Delete the table and use it if data exists in the table
Be careful with your methods! '''
if table is not None and table != '':
sql = 'DROP TABLE IF EXISTS ' + table
if SHOW_SQL:
print(' perform sql:[{}]'.format(sql))
cu = get_cursor(conn)
cu.execute(sql)
conn.commit()
print(' Delete database tables [{}] successful !'.format(table))
close_all(conn, cu)
else:
print('the [{}] is empty or equal None!'.format(sql))
def create_table(conn, sql):
''' Create database tables: student'''
if sql is not None and sql != '':
cu = get_cursor(conn)
if SHOW_SQL:
print(' perform sql:[{}]'.format(sql))
cu.execute(sql)
conn.commit()
print(' Creating database tables [student] successful !')
close_all(conn, cu)
else:
print('the [{}] is empty or equal None!'.format(sql))
###############################################################
#### create | Delete table operation END
###############################################################
def close_all(conn, cu):
''' Close the database cursor object and the database connection object '''
try:
if cu is not None:
cu.close()
finally:
if cu is not None:
cu.close()
###############################################################
#### Database operation CRUD START
###############################################################
def save(conn, sql, data):
''' Insert data '''
if sql is not None and sql != '':
if data is not None:
cu = get_cursor(conn)
for d in data:
if SHOW_SQL:
print(' perform sql:[{}], parameter :[{}]'.format(sql, d))
cu.execute(sql, d)
conn.commit()
close_all(conn, cu)
else:
print('the [{}] is empty or equal None!'.format(sql))
def fetchall(conn, sql):
''' Query all data '''
if sql is not None and sql != '':
cu = get_cursor(conn)
if SHOW_SQL:
print(' perform sql:[{}]'.format(sql))
cu.execute(sql)
r = cu.fetchall()
if len(r) > 0:
for e in range(len(r)):
print(r[e])
else:
print('the [{}] is empty or equal None!'.format(sql))
def fetchone(conn, sql, data):
''' The query 1 The data '''
if sql is not None and sql != '':
if data is not None:
#Do this instead
d = (data,)
cu = get_cursor(conn)
if SHOW_SQL:
print(' perform sql:[{}], parameter :[{}]'.format(sql, data))
cu.execute(sql, d)
r = cu.fetchall()
if len(r) > 0:
for e in range(len(r)):
print(r[e])
else:
print('the [{}] equal None!'.format(data))
else:
print('the [{}] is empty or equal None!'.format(sql))
def update(conn, sql, data):
''' Update the data '''
if sql is not None and sql != '':
if data is not None:
cu = get_cursor(conn)
for d in data:
if SHOW_SQL:
print(' perform sql:[{}], parameter :[{}]'.format(sql, d))
cu.execute(sql, d)
conn.commit()
close_all(conn, cu)
else:
print('the [{}] is empty or equal None!'.format(sql))
def delete(conn, sql, data):
''' Delete the data '''
if sql is not None and sql != '':
if data is not None:
cu = get_cursor(conn)
for d in data:
if SHOW_SQL:
print(' perform sql:[{}], parameter :[{}]'.format(sql, d))
cu.execute(sql, d)
conn.commit()
close_all(conn, cu)
else:
print('the [{}] is empty or equal None!'.format(sql))
###############################################################
#### Database operation CRUD END
###############################################################
###############################################################
#### The test operation START
###############################################################
def drop_table_test():
''' Delete database table tests '''
print(' Delete database table tests ...')
conn = get_conn(DB_FILE_PATH)
drop_table(conn, TABLE_NAME)
def create_table_test():
''' Create database table tests '''
print(' Create database table tests ...')
create_table_sql = '''CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`gender` varchar(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
)'''
conn = get_conn(DB_FILE_PATH)
create_table(conn, create_table_sql)
def save_test():
''' Save data test ...'''
print(' Save data test ...')
save_sql = '''INSERT INTO student values (?, ?, ?, ?, ?, ?)'''
data = [(1, 'Hongten', ' male ', 20, ' Guangzhou city, Guangdong Province ', '13423****62'),
(2, 'Tom', ' male ', 22, ' San Francisco, USA ', '15423****63'),
(3, 'Jake', ' female ', 18, ' Guangzhou city, Guangdong Province ', '18823****87'),
(4, 'Cate', ' female ', 21, ' Guangzhou city, Guangdong Province ', '14323****32')]
conn = get_conn(DB_FILE_PATH)
save(conn, save_sql, data)
def fetchall_test():
''' Query all data ...'''
print(' Query all data ...')
fetchall_sql = '''SELECT * FROM student'''
conn = get_conn(DB_FILE_PATH)
fetchall(conn, fetchall_sql)
def fetchone_test():
''' The query 1 The data ...'''
print(' The query 1 The data ...')
fetchone_sql = 'SELECT * FROM student WHERE ID = ? '
data = 1
conn = get_conn(DB_FILE_PATH)
fetchone(conn, fetchone_sql, data)
def update_test():
''' Update the data ...'''
print(' Update the data ...')
update_sql = 'UPDATE student SET name = ? WHERE ID = ? '
data = [('HongtenAA', 1),
('HongtenBB', 2),
('HongtenCC', 3),
('HongtenDD', 4)]
conn = get_conn(DB_FILE_PATH)
update(conn, update_sql, data)
def delete_test():
''' Delete the data ...'''
print(' Delete the data ...')
delete_sql = 'DELETE FROM student WHERE NAME = ? AND ID = ? '
data = [('HongtenAA', 1),
('HongtenCC', 3)]
conn = get_conn(DB_FILE_PATH)
delete(conn, delete_sql, data)
###############################################################
#### The test operation END
###############################################################
def init():
''' Initialization method '''
# Database file terminative path
global DB_FILE_PATH
DB_FILE_PATH = 'c:\\test\\hongten.db'
# Database table name
global TABLE_NAME
TABLE_NAME = 'student'
# Whether or not to print sql
global SHOW_SQL
SHOW_SQL = True
print('show_sql : {}'.format(SHOW_SQL))
# If a database table exists, the table is deleted
drop_table_test()
# Creating database tables student
create_table_test()
# Inserts data into the database table
save_test()
def main():
init()
fetchall_test()
print('#' * 50)
fetchone_test()
print('#' * 50)
update_test()
fetchall_test()
print('#' * 50)
delete_test()
fetchall_test()
if __name__ == '__main__':
main()
More about Python related content interested readers to view this site project: "common database operations Python skills summary", "Python data structure and algorithm tutorial", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"
I hope this article has been helpful in Python programming.